﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace SQLServerDAL
{
    public class URLInfo:IDAL.IURLInfo
    {
        /// <summary>
        /// 添加网址实体
        /// </summary>
        /// <param name="ui">网址实体</param>
        /// <returns>受影响的行数</returns>
        public int AddURLInfo(Model.URLInfo ui)
        {
            string sql = "insert into urlinfo values(@Title,@Remark,@Url,@Img,@FID)";
            SqlParameter[] pars ={
                                    new SqlParameter("@Title",ui.Title),
                                    new SqlParameter("@Remark",ui.Remark),
                                    new SqlParameter("@Url",ui.Url),
                                    new SqlParameter("@Img",ui.Img),
                                    new SqlParameter("@FID",ui.FID)
                                 };
            return SqlHelp.ExecuteSql(sql, pars);

        }
        /// <summary>
        /// 根据网址编号查询得到网址实体
        /// </summary>
        /// <param name="urid">网址编号</param>
        /// <returns>网址实体</returns>
        public Model.URLInfo GetURLInfoPK(int urid)
        {

            string sql = "select * from urlinfo where urid=@URID";
            SqlParameter[] pars = { 
                                    new SqlParameter("@URID",urid)
                                  };
            SqlDataReader dr = SqlHelp.ExecuteReader(sql, pars);
            IList<Model.URLInfo> list = GetURLInfo(dr);
            return list.Count == 1 ? list[0] : null;
        }
        /// <summary>
        /// 通过收藏夹分类查询网址实体
        /// </summary>
        /// <param name="fid">收藏编号</param>
        /// <returns>网址实体集合</returns>
        public IList<Model.URLInfo> GetURLInfoByFID(int fid)
        {

            string sql = "select * from urlinfo where fid=@FID";
            SqlParameter[] pars = { new SqlParameter("@FID", fid) };
            SqlDataReader dr = SqlHelp.ExecuteReader(sql, pars);
            IList<Model.URLInfo> list = GetURLInfo(dr);
            return list.Count > 0 ? list : null;
        }
        /// <summary>
        /// 封装数据
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private IList<Model.URLInfo> GetURLInfo(SqlDataReader dr)
        {
            IList<Model.URLInfo> list = new List<Model.URLInfo>();
            while (dr.Read())
            {
                Model.URLInfo uri = new Model.URLInfo();
                uri.URID = dr.GetInt32(0);
                uri.Title = dr.GetString(1);
                uri.Remark = dr.GetString(2);
                uri.Url = dr.GetString(3);
                uri.Img = dr.GetString(4);
                uri.FID = dr.GetInt32(5);
                list.Add(uri);

            }
            dr.Close();
            return list;
        }
        /// <summary>
        /// 通过网址夹查询网址信息
        /// </summary>
        /// <param name="fid">网址夹编号</param>
        /// <returns>数据集</returns>
        public DataSet GetURLInfoDataSet(int fid) 
        {
            string sql = "select * from urlinfo where fid=@FID";
            SqlParameter[] pars = { new SqlParameter("@FID", fid) };
            return SqlHelp.Query(sql, pars);
        }
        /// <summary>
        /// 修改网址信息
        /// </summary>
        /// <param name="ui">网址实体</param>
        /// <returns>受影响的行数</returns>
        public int UpdateURLInfo(Model.URLInfo ui) {
            string sql = "update urlinfo set title=@Title,remark=@Remark,url=@URL,Img=@Img,FID=@FID where urid=@URID";
            SqlParameter[] pars ={
                                    new SqlParameter("@Title",ui.Title),
                                    new SqlParameter("@Remark",ui.Remark),
                                    new SqlParameter("@URL",ui.Url),
                                    new SqlParameter("@Img",ui.Img),
                                    new SqlParameter("@FID",ui.FID),
                                    new SqlParameter("@URID",ui.URID)
                                 };
            return SqlHelp.ExecuteSql(sql, pars);
        }
        /// <summary>
        /// 删除网址信息
        /// </summary>
        /// <param name="urid">编号</param>
        /// <returns>受影响的行数</returns>
        public int DeleteURLInfo(int urid) { 
             string sql = "delete from urlinfo where urid=@URID";
            SqlParameter[] pars ={
                                    
                                    new SqlParameter("@URID",urid)
                                 };
            return SqlHelp.ExecuteSql(sql, pars);
        }
    }
}
